using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using Excel = Microsoft.Office.Interop.Excel;

namespace Glpt.Office
{
    /// <summary>
    /// 读取Excel类
    /// </summary>
    public class ImportExcel
    {
        /// <summary>
        /// 初始化类的新实例
        /// </summary>
        public ImportExcel()
        {
        }

        /// <summary>
        /// 以数据表形式读取数据
        /// </summary>
        /// <param name="file_path">Excle文件名</param>
        public DataTable Read(string file_path)
        {
            return Read(file_path, "0");
        }
        /// <summary>
        /// 以数据表形式读取数据
        /// </summary>
        /// <param name="file_path">Excle文件名</param>
        /// <param name="read_type">读取模式：0以数据表方式读取，1：以表格样式读取，2：以坐标读取</param>
        public DataTable Read(string file_path, string read_type)
        {
            return Read(file_path, read_type, "");
        }
        /// <summary>
        /// 以数据表形式读取数据
        /// </summary>
        /// <param name="file_path">Excle文件名</param>
        /// <param name="read_type">读取模式：0以数据表方式读取，1：以表格样式读取，2：以坐标读取</param>
        /// <param name="sheet_name">表名</param>
        public DataTable Read(string file_path, string read_type, string sheet_name)
        {
            return Read(file_path, read_type, sheet_name, "");
        }
        /// <summary>
        /// 以数据表形式读取数据
        /// </summary>
        /// <param name="file_path">Excle文件名</param>
        /// <param name="read_type">读取模式：0以数据表方式读取，1：以表格样式读取，2：以坐标读取</param>
        /// <param name="sheet_name">表名</param>
        /// <param name="read_params">读取参数</param>
        public DataTable Read(string file_path, string read_type, string sheet_name, string read_params)
        {
            DataTable dTable = new DataTable();
            string sheetname = sheet_name.Trim();
            int nullrow = 0;//保留空行的行数
            bool open_excle = false;//打开excel
            string[] zb = read_params.Split(new char[] { ':' }, StringSplitOptions.None);
            if (zb.Length > 0 && zb[0].Length > 0) int.TryParse(zb[0], out nullrow);
            if (zb.Length > 1) open_excle = zb[1].Equals("1");
            if (file_path.ToLower().EndsWith(".csv"))
            {
                dTable = ReadCSV(file_path);
            }
            else if (read_type.Equals("0") && !open_excle)
            {
                dTable = ReadExcel(file_path, sheetname);
            }
            else
            {
                Excel.Range m_objRange = null;
                object m_objOpt = null;
                Excel.Application m_objExcel = null;
                Excel.Workbooks m_objBooks = null;
                Excel._Workbook m_objBook = null;
                Excel.Sheets m_objSheets = null;
                Excel._Worksheet m_objSheet = null;
                try
                {
                    m_objOpt = System.Reflection.Missing.Value;
                    m_objExcel = new Excel.Application();
                    m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
                    m_objBook = m_objBooks.Open(file_path, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
                    m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
                    if (sheetname.Length == 0)
                    {
                        m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
                        sheetname = m_objSheet.Name;
                    }
                    else
                    {
                        int idx = 1;
                        if (sheetname.Length <= 2 && int.TryParse(sheetname, out idx))
                        {
                            m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(idx));
                            sheetname = m_objSheet.Name;
                        }
                        else
                        {
                            bool has_flag = false;
                            for (int i = 1; i <= m_objSheets.Count; i++)
                            {
                                m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(i));
                                if (sheetname.Equals(m_objSheet.Name))
                                {
                                    has_flag = true;
                                    break;
                                }
                            }
                            if (!has_flag) throw new Exception("找不到指定的表名：" + sheetname);
                        }
                    }
                    if (read_type.Equals("1"))//找表格线
                    {
                        int startrow = 0;
                        int startcol = 0;
                        int endrow = 0;
                        int endcol = 0;
                        for (int irow = 1; irow <= m_objSheet.UsedRange.Rows.Count; irow++)
                        {
                            if (startrow == 0 && startcol == 0)
                            {
                                for (int icol = 1; icol <= m_objSheet.UsedRange.Columns.Count; icol++)
                                {
                                    m_objRange = (Excel.Range)m_objSheet.UsedRange.Cells[irow, icol];
                                    object clinestyle = m_objRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).LineStyle;
                                    if (CheckTableStyle(clinestyle))
                                    {
                                        if (startrow == 0) startrow = irow;
                                        if (startcol == 0) startcol = icol;
                                        endcol = icol;
                                    }
                                }
                            }
                            else
                            {
                                m_objRange = (Excel.Range)m_objSheet.UsedRange.Cells[irow, startcol];
                                object clinestyle = m_objRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).LineStyle;
                                if (CheckTableStyle(clinestyle))
                                    endrow = irow;
                                else
                                    break;
                            }
                        }
                        if (startrow > 0 && startcol > 0)
                        {
                            endrow = endrow - startrow + 1;
                            endcol = endcol - startcol + 1;
                            m_objRange = (Excel.Range)m_objSheet.UsedRange.Cells[startrow, startcol];
                            m_objRange = m_objRange.get_Resize(endrow, endcol);
                            object[,] vals = m_objRange.Value;
                            dTable = GetData(vals, 1, 1, endrow, endcol);
                        }
                    }
                    else if (read_type.Equals("2"))//按坐标
                    {
                        nullrow = 0;
                        int firstrow = m_objSheet.UsedRange.Row;
                        int firstcol = m_objSheet.UsedRange.Column;
                        int startrow = firstrow;
                        int startcol = firstcol;
                        int i_rowend = 0;
                        int colcount = 0;
                        int findrow = 0;
                        int findcol = 0;
                        bool is_endrow = false;
                        if (zb.Length > 0 && zb[0].Length > 0)
                        {
                            string strrow = "";
                            int idx = zb[0].LastIndexOf('#');//查找定位
                            if (idx >= 0)
                            {
                                string val = zb[0].Substring(0, idx);
                                Excel.Range oRange = m_objSheet.UsedRange.Find(val, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSearchDirection.xlNext, m_objOpt, m_objOpt);
                                if (oRange != null)
                                {
                                    findrow = oRange.Row;
                                    findcol = oRange.Column;
                                }
                                else
                                    throw new Exception("没有找到指定的字符串" + val);
                                strrow = zb[0].Substring(idx + 1);
                            }
                            else
                                strrow = zb[0];
                            idx = strrow.LastIndexOf('_');//结束行
                            if (idx >= 0)
                            {
                                startrow = int.Parse(strrow.Substring(0, idx));
                                i_rowend = int.Parse(strrow.Substring(idx + 1));
                                is_endrow = true;
                            }
                            else
                                startrow = int.Parse(strrow);
                        }
                        if (zb.Length > 1)
                        {
                            int idx = zb[1].LastIndexOf('_');//结束列
                            if (idx >= 0)
                            {
                                startcol = int.Parse(zb[1].Substring(0, idx));
                                colcount = int.Parse(zb[1].Substring(idx + 1));
                                colcount = colcount - startcol + 1;
                            }
                            else
                                startcol = int.Parse(zb[1]);
                        }
                        if (zb.Length > 2 && zb[2].Length > 0) nullrow = int.Parse(zb[2]);
                        bool hastitle = true;
                        if (zb.Length > 3 && zb[3].Length > 0) hastitle = zb[3].Equals("1");
                        startrow = startrow + findrow;
                        startcol = startcol + findcol;
                        if (is_endrow) i_rowend = i_rowend + findrow;
                        int endrow = m_objSheet.UsedRange.Rows.Count + firstrow - startrow;
                        if (i_rowend >= startrow) endrow = i_rowend - startrow + 1;
                        if (endrow > i_rowend && i_rowend > startrow) endrow = i_rowend;
                        int endcol = m_objSheet.UsedRange.Columns.Count + firstcol - startcol;
                        m_objRange = (Excel.Range)m_objSheet.Cells[startrow, startcol];
                        m_objRange = m_objRange.get_Resize(1, endcol);
                        object[,] vals = null;
                        if (m_objRange.Value is Array)
                            vals = m_objRange.Value;
                        else
                        {
                            vals = new object[2, 2];
                            vals[1, 1] = m_objRange.Value;
                        }
                        List<string> column_list = new List<string>();
                        for (int icol = 1; icol <= endcol; icol++)
                        {
                            if (colcount > 0 && icol > colcount)
                                break;
                            string fieldname = "";
                            if (vals[1, icol] != null)
                                fieldname = vals[1, icol].ToString().Trim();
                            column_list.Add(fieldname);
                        }
                        for (int i = column_list.Count - 1; i >= 0; i--)
                        {
                            if (colcount > 0 && i < colcount)
                                break;
                            if (column_list[i].Length == 0)
                                column_list.RemoveAt(i);
                            else
                                break;
                        }
                        endcol = column_list.Count;
                        if (endcol > 0)
                        {
                            m_objRange = m_objRange.get_Resize(endrow, endcol);
                            if (m_objRange.Value is Array)
                                vals = m_objRange.Value;
                            else
                            {
                                vals = new object[2, 2];
                                vals[1, 1] = m_objRange.Value;
                            }
                            dTable = GetData(vals, hastitle ? 1 : 0, 1, endrow, endcol);
                        }
                    }
                    else
                    {
                        dTable = ReadExcel(file_path, sheetname);
                    }
                }
                finally
                {
                    if (m_objBook != null)
                        m_objBook.Close(false, m_objOpt, m_objOpt);
                    if (m_objExcel != null)
                        m_objExcel.Quit();
                    if (m_objBook != null)
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
                    if (m_objBooks != null)
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
                    if (m_objSheets != null)
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
                    if (m_objExcel != null)
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
                    m_objOpt = null;
                    m_objRange = null;
                    m_objSheet = null;
                    m_objSheets = null;
                    m_objBook = null;
                    m_objBooks = null;
                    m_objExcel = null;
                    GC.Collect();
                }
            }
            RemoveRows(dTable, nullrow);
            return dTable;
        }

        private DataTable GetData(object[,] vals, int startrow, int startcol, int endrow, int endcol)
        {
            DataTable dTable = new DataTable();
            List<string> column_list = new List<string>();
            for (int icol = startcol; icol <= endcol; icol++)
            {
                string fieldname = "";
                if (startrow == 0)
                    fieldname = "F" + icol.ToString();
                else if (vals[startrow, icol] != null)
                    fieldname = vals[startrow, icol].ToString().Trim().Replace("\n", "");
                column_list.Add(fieldname);
            }
            for (int i = 0; i < column_list.Count; i++)
            {
                int icol = i + 1;
                string fieldname = column_list[i];
                if (fieldname.Length == 0)
                    fieldname = "F" + icol.ToString();
                string fieldname2 = fieldname;
                int v = 1;
                while (dTable.Columns.Contains(fieldname2))
                {
                    fieldname2 = fieldname + (v++).ToString();
                }
                dTable.Columns.Add(fieldname2, typeof(string));
            }
            for (int irow = startrow + 1; irow <= endrow; irow++)
            {
                DataRow dr = dTable.NewRow();
                for (int icol = 0; icol < dTable.Columns.Count; icol++)
                {
                    dr[icol] = vals[irow, startcol + icol];
                }
                dTable.Rows.Add(dr);
            }
            return dTable;
        }
        private bool CheckTableStyle(object clinestyle)
        {
            if (clinestyle == null || clinestyle.Equals(DBNull.Value) || (int)clinestyle == (int)Excel.XlLineStyle.xlLineStyleNone)
                return false;
            else
                return true;
        }
        /// <summary>
        /// 读取CSV数据
        /// </summary>
        /// <param name="file_path">文件名</param>
        public static DataTable ReadCSV(string file_path)
        {
            System.IO.FileInfo file = new System.IO.FileInfo(file_path);
            string selectconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file.DirectoryName + ";Extended Properties='Text;HDR=YES;IMEX=1'";
            OleDbDataAdapter ada = new OleDbDataAdapter("SELECT * FROM [" + file.Name + "]", selectconn);
            DataTable dTable = new DataTable();
            ada.Fill(dTable);
            return dTable;
        }
        /// <summary>
        /// 读取Excel数据
        /// </summary>
        /// <param name="file_path">文件名</param>
        /// <param name="sheet_name">表名</param>
        /// <returns>数据</returns>
        public static DataTable ReadExcel(string file_path, string sheet_name)
        {
            string selectconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file_path + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
            int idx = 1;
            if (sheet_name.Length == 0)
                sheet_name = idx.ToString();
            if (sheet_name.Length <= 2 && int.TryParse(sheet_name, out idx))
            {
                idx = idx - 1;
                using (OleDbConnection conn = new OleDbConnection(selectconn))
                {
                    conn.Open();
                    DataTable dtname = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
                    if (dtname.Rows.Count > 0)
                        sheet_name = dtname.Rows[idx][2].ToString();//得到sheet名
                    conn.Close();
                }
            }
            else
                sheet_name = sheet_name + "$";
            OleDbDataAdapter ada = new OleDbDataAdapter("SELECT * FROM [" + sheet_name + "]", selectconn);
            DataTable dTable = new DataTable();
            ada.Fill(dTable);
            return dTable;
        }
        /// <summary>
        /// 删除空行
        /// </summary>
        /// <param name="dTable">数据表</param>
        /// <param name="nullrow">删除第N个空行后的记录，-1只删除最后面的空行,-2删除全部空行</param>
        public static void RemoveRows(DataTable dTable, int nullrow)
        {
            if (nullrow < 0)
            {
                for (int i = dTable.Rows.Count - 1; i >= 0; i--)
                {
                    bool rowisnull = true;
                    for (int j = 0; j < dTable.Columns.Count; j++)
                    {
                        if (dTable.Rows[i][j].ToString().Length != 0)
                        {
                            rowisnull = false;
                            break;
                        }
                    }
                    if (rowisnull)
                        dTable.Rows.RemoveAt(i);
                    else if (nullrow == -1)
                        break;
                }
            }
            else
            {
                int ino = 0;
                for (int i = 0; i < dTable.Rows.Count; i++)
                {
                    bool rowisnull = true;
                    for (int j = 0; j < dTable.Columns.Count; j++)
                    {
                        if (dTable.Rows[i][j].ToString().Length != 0)
                        {
                            rowisnull = false;
                            break;
                        }
                    }
                    if (rowisnull)
                    {
                        ino++;
                        if (ino > nullrow)
                        {
                            for (int j = dTable.Rows.Count - 1; j >= i; j--)
                            {
                                dTable.Rows.RemoveAt(j);
                            }
                            break;
                        }
                    }
                }
            }
            dTable.AcceptChanges();
        }

        /// <summary>
        /// 释放内存
        /// </summary>
        public void Dispose()
        {
            try
            {
                GC.Collect();
            }
            catch { }
        }

        /// <summary>
        /// 析构函数
        /// </summary>
        ~ImportExcel()
        {
            Dispose();
        }
    }
}
